package net.groupme.utils.sql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import net.groupme.model.Group;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactoryProperties;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * 
 * created on 09.07.2007
 * 
 * This class provides DB querying method for detecting groups.
 * 
 * @author Mischa Frank, <a href="mailto:code@planetfrank.net">code@planetfrank.net</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.7 $ $Date: 2009-04-22 18:28:55 $
 */
public class GroupQueryingUtility {

	/** the data source (= backend for DAO implementation) */
	static DriverManagerDataSource ds = new DriverManagerDataSource();
	static {
		ds.setDriverClassName(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_DRIVER")); //$NON-NLS-1$
		ds.setUrl(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_URL")); //$NON-NLS-1$
		ds.setUsername(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_USER")); //$NON-NLS-1$
		ds.setPassword(ObjectFactoryProperties.getString("ObjectFactoryProperties.DB_PW")); //$NON-NLS-1$
	}
	
	/**
	 * Returns all groups in GroupMe!
	 * @return a List of all Groups in GroupMe!
	 */
	@SuppressWarnings("unchecked")
	public static List<Group> queryForAllGroups(){
		String query= "SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE
						+ " WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + " = '" + DATABASE_CONSTANTS.RESOURCE_TYPE_GROUP + "' ORDER BY " + DATABASE_CONSTANTS.RESOURCE_NAME;
		GetGroupsQuery groupsQuery = new GetGroupsQuery(ds, query, DATABASE_CONSTANTS.RESOURCE_ID);
		Iterator iter = groupsQuery.execute().iterator();
		if (iter.hasNext()) {
			return (List<Group>) iter.next();	
		}
		return new ArrayList<Group>();
	}
	
	/**
	 * This method returns all groups of the user. This 
	 * method returns an ordered list. The first element is the group is the newest modified group. 
	 * @param user the user who assigned the tag and owns the group
	 * @param role the role the user should have concerning the groups (if this is <code>null</code> then it can be any role
	 * @return ranking of most actual groups or an empty list if no groups are found
	 */
	@SuppressWarnings("unchecked")
	public static List<Group> getAllGroupsOfUser(User user, String[] roles){
		String query = "SELECT " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP  + 
			" WHERE " + getRolesConditions(roles) + " AND "
				+ DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' order by " + DATABASE_CONSTANTS.USER_GROUP_TAD;
		GetGroupsQuery getGroups = new GetGroupsQuery(ds, query, DATABASE_CONSTANTS.USER_GROUP_GROUP_ID);
		Iterator iter = getGroups.execute().iterator();
		if (iter.hasNext()) {
			return (List<Group>) getGroups.execute().iterator().next();
		}
		return new ArrayList<Group>();
	}
	
	/**
	 * This method returns all groups of the user. This method returns an ordered list. 
	 * The order is specified by a param.
	 * @param user the user who assigned the tag and owns the group
	 * @param role the role the user should have concerning the groups (if this is <code>null</code> then it can be any role
	 ' @param orderBy the indicator for the order of the list
	 * @return ordered grouplist or empty list if no groups are found
	 */
	@SuppressWarnings("unchecked")
	public static List<Group> getAllGroupsOfUserOrdered(User user, String[] roles, String orderBy){
		String query = "SELECT DISTINCT " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP  +
			" JOIN " + DATABASE_CONSTANTS.TABLE_RESOURCE + " ON " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " = " +
			DATABASE_CONSTANTS.RESOURCE_ID + " WHERE " + getRolesConditions(roles) + " AND "
				+ DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' order by " + orderBy;
		GetGroupsQuery getGroups = new GetGroupsQuery(ds, query, DATABASE_CONSTANTS.USER_GROUP_GROUP_ID);
		Iterator iter = getGroups.execute().iterator();
		if (iter.hasNext()) {
			return (List<Group>) getGroups.execute().iterator().next();
		}
		return new ArrayList<Group>();
	}
	
	private static String getRolesConditions(String[] roles){
		String roleString = null;
		for (int i = 0; i < roles.length; i++) {
			if(roleString == null){
				roleString = " (" + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + roles[i] + "' ";
			}else{
				roleString += " OR " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + roles[i] + "' ";
			}
		}
		if(roleString != null){
			roleString += " ) ";
		}
		return roleString;
	}
	/**
	 * This method returns all users of the group. 
	 * @param groupId the group the users are searched for
	 * @param role the role the users should have concerning the group (if this is <code>null</code> then it can be any role
	 * @return all users which of the group with the specified role
	 */
	@SuppressWarnings("unchecked")
	public static List<User> getAllUsersOfGroup(String groupId, String role){
		String query = "SELECT " + DATABASE_CONSTANTS.USER_GROUP_USER_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + 
			" WHERE " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + role + "' AND "
				+ DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " = '" + groupId + "'";
		GetUsersQuery getUsers = new GetUsersQuery(ds, query, DATABASE_CONSTANTS.USER_GROUP_USER_ID);
		Iterator iter = getUsers.execute().iterator();
		if (iter.hasNext()) {
			return (List<User>) getUsers.execute().iterator().next();
		}
		return new ArrayList<User>();
	}
	
	/**
	 * This method returns those groups of the user that have been created at last. This 
	 * method returns an ordered list. The first element is the group is the newest modified group. 
	 * @param user the user who assigned the tag and owns the group
	 * @param topK the top K (K newest groups) - number of groups that shold be contained in the ranking
	 * @param role the role the user should have concerning the groups (if this is <code>null</code> then it can be any role
	 * @return ranking of most actual groups or an empty list if no groups are found
	 */
	@SuppressWarnings("unchecked")
	public static List<Group> getLatestGroupsOfUser(User user, String roles[], int topK){
		String query = "SELECT " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP  + 
			" WHERE " + getRolesConditions(roles) + " AND "
				+ DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' order by " + DATABASE_CONSTANTS.USER_GROUP_TAD+ " desc limit " + topK;
		GetGroupsQuery getGroups = new GetGroupsQuery(ds, query, DATABASE_CONSTANTS.USER_GROUP_GROUP_ID);
		Iterator iter = getGroups.execute().iterator();
		if (iter.hasNext()) {
			return (List<Group>) getGroups.execute().iterator().next();
		}
		return new ArrayList<Group>();
	}
	
	/**
	 * This method returns the newest Groups in GroupMe!. This 
	 * method returns an ordered list. The first element is the group is the newest group. 
	 * @param topK the top K (K newest groups) - number of groups that shold be contained in the ranking
	 * @return ranking of most actual groups or an empty list if no groups are found
	 */
	@SuppressWarnings("unchecked")
	public static List<Group> getXLatestGroups(int topK){
		String query = "SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE + 
			" WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE+ " = '" + DATABASE_CONSTANTS.RESOURCE_TYPE_GROUP + 
			"' order by " + DATABASE_CONSTANTS.RESOURCE_TAD + " desc limit " + topK;
		GetGroupsQuery getGroups = new GetGroupsQuery(ds, query, DATABASE_CONSTANTS.RESOURCE_ID);
		Iterator iter = getGroups.execute().iterator();
		if (iter.hasNext()) {
			return (List<Group>) getGroups.execute().iterator().next();
		}
		return new ArrayList<Group>();
	}
}
